package DB;

import DB.Evento.EventoTable;
import DB.Boleto.BoletoTable;
import DB.Usuario.UsuariosTable;
import DB.Inscricao.InscricaoTable;
import DB.Controle.ControleTable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBManager {

    private static DBManager db;
    private Connection conn;
    private Statement stm;
    private final String DatabaseName;
    private final String DROP_COMMAND = "DROP TABLE IF EXISTS ";

    public static DBManager getInstance() {
        if (db == null) {
            db = new DBManager();
        }

        return db;
    }
    
    public static DBManager getInstance(String dbName) {
        if (db == null) {
            db = new DBManager(dbName);
        }

        return db;
    }
    
    public DBManager() {
        this.DatabaseName = "PMI.db";
        try {
            Class.forName("org.sqlite.JDBC");
            this.conn = DriverManager.getConnection("jdbc:sqlite:" + DatabaseName);

            initDB();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace(System.err);
            System.exit(0);
        }
    }
    
    public DBManager(String dbName) {
        this.DatabaseName = dbName + ".db";
        try {
            Class.forName("org.sqlite.JDBC");
            this.conn = DriverManager.getConnection("jdbc:sqlite:" + DatabaseName);

            initDB();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace(System.err);
            System.exit(0);
        }
    }

    private void initDB() {
        try {
            System.out.println("Criando novo DBManager...");
            stm = conn.createStatement();

            /**
             * Descomentar a linha abaixo pra limpar as tabelas em todas as 
             * inicializações do programa.
             */
//            dropTables();

            /**
             * Cria as tabelas, indexes e views se elas já não existirem no banco de dados.
             */
            createTables();
            createIndexes();
            createViews();
            
            /**
             * Exibe a quantidade de registros existentes em cada tabela.
             */
            System.out.println("Table usuarios possui: " + this.getTableCount(UsuariosTable.TABLE_NAME));
            System.out.println("Table eventos possui: " + this.getTableCount(EventoTable.TABLE_NAME));
            System.out.println("Table inscricoes possui: " + this.getTableCount(InscricaoTable.TABLE_NAME));
            System.out.println("Table boleto possui: " + this.getTableCount(BoletoTable.TABLE_NAME));
            System.out.println("Table controle possui: " + this.getTableCount(ControleTable.TABLE_NAME));
            System.out.println();

//            Calendar c = Calendar.getInstance();
//            SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
//            System.out.println("Hoje é: " + df.format(c.getTime()));
        } catch (SQLException e) {
            e.printStackTrace(System.err);
            System.exit(1);
        }
    }
        
    public Connection getConn() {
        return conn;
    }
    
    /**
     * Cria as tabelas, se elas já não existirem no banco de dados.
     */
    private void createTables() {
        try {
            stm.executeUpdate(UsuariosTable.TABLE_CREATE);
            stm.executeUpdate(EventoTable.TABLE_CREATE);
            stm.executeUpdate(InscricaoTable.TABLE_CREATE);
            stm.executeUpdate(InscricaoTable.CREATE_INDEX_ID_ID);
            stm.executeUpdate(BoletoTable.TABLE_CREATE);
            stm.executeUpdate(ControleTable.TABLE_CREATE);
        } catch (SQLException e) {
            System.err.println("Deu merda durante o comando createTables na inicialização do programa");
            e.printStackTrace(System.err);
            System.exit(1);
        }
    }

    /**
     * Cria os indexes, se elas já não existirem no banco de dados.
     */
    private void createIndexes() {
        try {
            stm.executeUpdate(InscricaoTable.CREATE_INDEX_ID_ID);
        } catch (SQLException e) {
//            System.err.println("Deu merda durante o comando " + Thread.currentThread().getStackTrace()[1].getMethodName() + " na inicialização do programa");
            System.err.println("Deu merda durante o comando createIndexes na inicialização do programa");
            e.printStackTrace(System.err);
            System.exit(1);
        }
    }

    /**
     * Cria as views, se elas já não existirem no banco de dados.
     */
    private void createViews() {
        try {
            stm.executeUpdate(Views.dropViewInscricao);
            int i = stm.executeUpdate(Views.createViewInscricao);
            System.out.println("createViews retornou: " + i);
        } catch (SQLException e) {
            System.err.println("Deu merda durante o comando createViws na inicialização do programa");
            e.printStackTrace(System.err);
            System.exit(1);
        }
    }

    /**
     * Dropa as tabelas.
     */
    private void dropTables() {
        try {
//            stm.executeUpdate(DROP_COMMAND + UsuariosTable.TABLE_NAME);
            stm.executeUpdate(DROP_COMMAND + EventoTable.TABLE_NAME);
//            stm.executeUpdate(DROP_COMMAND + InscricaoTable.TABLE_NAME);
//            stm.executeUpdate(DROP_COMMAND + BoletoTable.TABLE_NAME);
//            stm.executeUpdate(DROP_COMMAND + ControleTable.TABLE_NAME);
        } catch (SQLException e) {
            System.err.println("Deu merda durante o comando dropTables na inicialização do programa");
            e.printStackTrace(System.err);
            System.exit(1);
        }
    }

    /**
     * Retorna a quantidade de registros da tabela informada.
     *
     * @param tableName O nome da tabela.
     * @return a quantidade da registros da tabela informada.
     */
    public int getTableCount(String tableName) {
        ResultSet rs;
        int count = 0;

        try {
            this.stm = this.conn.createStatement();
            String query = "SELECT COUNT(*) FROM " + tableName;

            rs = this.stm.executeQuery(query);
            rs.next();
            count = rs.getInt(1);

        } catch (SQLException e) {
            e.printStackTrace(System.err);
            System.exit(0);
        }

        return count;
    }
    
    public void deleteAll() {
        try {
            stm.executeUpdate(UsuariosTable.DELETE_ALL);
            stm.executeUpdate(EventoTable.DELETE_ALL);
            stm.executeUpdate(InscricaoTable.DELETE_ALL);
            stm.executeUpdate(BoletoTable.DELETE_ALL);
            stm.executeUpdate(ControleTable.DELETE_ALL);
        } catch (SQLException e) {
            e.printStackTrace(System.err);
            System.exit(1);
        }
    }
}